package com.feiyu.common.util;

import com.feiyu.bean.HouseProject;
import com.feiyu.bean.HouseProjectChange;
import com.feiyu.bean.HouseUnit;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @author nanshouxiao@teacher.com.cn
 * @date 2018-05-16 9:33
 */
public class CustomExcelUtil {
    /**
     * excle2003之前的类型
     */
    private static final String EXCEL_2003L = "xls";

    /**
     * excle2007及之后的类型
     */
    private static final String EXCEL_2007U = "xlsx";

    static Set<String> houseTypeSet = Arrays.stream("毛坯项目,精装项目".split(",")).collect(Collectors.toSet());

    public static List<HouseProject> handleProjectExcel(MultipartFile file) {
        Sheet sheet = getSheet(file);
        return handleProjectExcel(sheet);
    }
    public static List<HouseProject> handleProjectExcel(File file) {
        Sheet sheet = getSheet(file);
        return handleProjectExcel(sheet);
    }

    /**
     * 从 sheet 里获取项目信息
     * @param sheet
     * @return
     */
    private static List<HouseProject> handleProjectExcel(Sheet sheet) {
        int columnlength = sheet.getLastRowNum();
        String decorateType = null;
        String lastOrderIndex = null;
        String lastProjectName = null;
        String lastDevelopCompany = null;
        String lastProjectAddress = null;
        List<HouseProject> houseProjectList = new ArrayList<>();
        for (int i = 2; i <= columnlength; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            String firstStr = getCellString(row.getCell(0));
            if (houseTypeSet.contains(firstStr)) {
                decorateType = firstStr;
                continue;
            }
            if (decorateType == null) {
                break;
            }

            String orderIndex = String.valueOf(getCellInteger(row.getCell(0)));
            String projectName = getCellString(row.getCell(1));
            String developCompany = getCellString(row.getCell(2));
            String projectAddress = getCellString(row.getCell(3));
            String buildingNumber = getCellString(row.getCell(4));
            BigDecimal totalArea = new BigDecimal(getCellString(row.getCell(5)));
            Integer totalUnitNumber = getCellInteger(row.getCell(6));
            if ("".equals(projectName)) {
                orderIndex = lastOrderIndex;
                projectName = lastProjectName;
                developCompany = lastDevelopCompany;
                projectAddress = lastProjectAddress;
            } else {
                lastOrderIndex = orderIndex;
                lastProjectName = projectName;
                lastDevelopCompany = developCompany;
                lastProjectAddress = projectAddress;
            }
            houseProjectList.add(new HouseProject(orderIndex, projectName, developCompany,
                    projectAddress, buildingNumber, totalArea, totalUnitNumber, decorateType));
        }
        return houseProjectList;
    }

    public static List<HouseUnit> handleUnitExcel(MultipartFile file) {
        Sheet[] sheets = getAllSheets(file);
        return handleUnitExcel(sheets);
    }
    public static List<HouseUnit> handleUnitExcel(File file) {
        Sheet[] sheets = getAllSheets(file);
        return handleUnitExcel(sheets);
    }

    public static List<HouseUnit> handleUnitExcel(Sheet[] sheets) {
        List<HouseUnit> totalHouseUnitList = new ArrayList<>();
        for (int i = 0; i < sheets.length; i++) {
            System.out.println("执行 文件 " +i );
            totalHouseUnitList.addAll(getHouseUnitList(sheets[i]));
        }
        return totalHouseUnitList;
    }

    private static List<HouseUnit> getHouseUnitList(Sheet sheet) {
        int columnlength = sheet.getLastRowNum();
        boolean isStart = false;
        String houseType = null;
        List<HouseUnit> houseProjectList = new ArrayList<>();
        String buildingNumber = null;
        String total_floor = null;
        for (int i = 1; i <= columnlength; i++) {
            System.out.println("执行行数" + i);
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            String firstStr = getCellString(row.getCell(0));
            if (firstStr.contains("楼幢号")) {
                buildingNumber = getCellString(row.getCell(1));
                total_floor = getCellString(row.getCell(3));
                continue;
            }
            if (firstStr.contains("序号")) {
                isStart = true;
                continue;
            }
            if (isStart) {
                HouseUnit houseUnit = new HouseUnit();
                houseUnit.setBuildingNumber(buildingNumber);
                Integer orderNumber = getCellInteger(row.getCell(0));
                if (orderNumber == null) {
                    continue;
                }
                houseUnit.setOrderNumber(orderNumber);
                houseUnit.setUnitNumber(getCellString(row.getCell(1)));
                houseUnit.setRoomArea(new BigDecimal(getCellString(row.getCell(2))));
                houseUnit.setPrice(new BigDecimal(getCellString(row.getCell(3))));
                houseUnit.setTotalPrice(new BigDecimal(getCellString(row.getCell(4))));
                houseUnit.setTotalFloor(total_floor);
                houseProjectList.add(houseUnit);
            }
        }
        return houseProjectList;

    }


    public static List<HouseProjectChange> handleProjectChangeExcel(MultipartFile file) {
        Sheet sheet = getSheet(file);
        return handleProjectChangeExcel(sheet);
    }
    public static List<HouseProjectChange> handleProjectChangeExcel(File file) {
        Sheet sheet = getSheet(file);
        return handleProjectChangeExcel(sheet);
    }

    /**
     * 从 sheet 里获取项目信息
     * @param sheet
     * @return
     */
    private static List<HouseProjectChange> handleProjectChangeExcel(Sheet sheet) {
        int columnlength = sheet.getLastRowNum();
        String lastOrderIndex = null;
        String lastProjectName = null;
        String lastDevelopCompany = null;
        String lastProjectAddress = null;
        String lastAreaName = null;
        String lastPlotRatio = null;
        List<HouseProjectChange> houseProjectList = new ArrayList<>();
        for (int i = 2; i <= columnlength; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            String firstStr = getCellString(row.getCell(0));
            String orderIndex = String.valueOf(getCellInteger(row.getCell(0)));
            String projectName = getCellString(row.getCell(1));
            String developCompany = getCellString(row.getCell(2));
            String projectAddress = getCellString(row.getCell(3));
            String areaName = getCellString(row.getCell(4));
            String plotRatio = getCellString(row.getCell(5));
            String buildingNumber = getCellString(row.getCell(6));
            String floorNumber = getCellString(row.getCell(7));
            String decorateType = getCellString(row.getCell(8));
            BigDecimal totalArea = new BigDecimal(getCellString(row.getCell(9)));
            Integer totalUnitNumber = Double.valueOf(row.getCell(10).getNumericCellValue()).intValue();
            if ("".equals(projectName)) {
                orderIndex = lastOrderIndex;
                projectName = lastProjectName;
                developCompany = lastDevelopCompany;
                projectAddress = lastProjectAddress;
                areaName = lastAreaName;
                plotRatio = lastPlotRatio;
            } else {
                lastOrderIndex = orderIndex;
                lastProjectName = projectName;
                lastDevelopCompany = developCompany;
                lastProjectAddress = projectAddress;
                lastAreaName = areaName;
                lastPlotRatio = plotRatio;
            }
            houseProjectList.add(
                    new HouseProjectChange(orderIndex, projectName, developCompany, projectAddress, areaName,
                            plotRatio, buildingNumber, floorNumber, decorateType, totalArea, totalUnitNumber)
            );

        }
        return houseProjectList;
    }


    private static String getCellString(Cell cell) {
        if (cell == null) {
            return "";
        }
        Object cellValue = getCellValue(cell);
        return String.valueOf(cellValue).trim();
    }

    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellTypeEnum()) {
            case BLANK:
                return "";
            case _NONE:
                return null;
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case NUMERIC:
                return cell.getNumericCellValue();
            case FORMULA:
                return cell.getNumericCellValue();
        }
        return null;
    }
    private static Double getCellDouble(Cell cell) {
        if (cell == null) {
            return null;
        }
        CellType cellTypeEnum = cell.getCellTypeEnum();
        if (cellTypeEnum.equals(CellType.BLANK)) {
            return null;
        }
        if (cellTypeEnum.equals(CellType.NUMERIC)) {
            return cell.getNumericCellValue();
        } else {
            String cellValue = cell.getStringCellValue();
            if (!cellValue.isEmpty()) {
                return Double.parseDouble(String.valueOf(cellValue).trim());
            }
        }
        return null;
    }
    private static Integer getCellInteger(Cell cell) {
        Double doubleValue = getCellDouble(cell);
        if (doubleValue == null) {
            return null;
        }
        return doubleValue.intValue();
    }

    public static Sheet getSheet(MultipartFile file) {
        Sheet sheet = null;
        try {
            sheet = getSheet(file.getInputStream(), file.getOriginalFilename());
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return sheet;
    }
    public static Sheet[] getAllSheets(MultipartFile file) {
        Sheet[] sheets = null;
        try {
            sheets = getAllSheets(file.getInputStream(), file.getOriginalFilename());
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return sheets;
    }
    public static Sheet getSheet(File file) {
        Sheet sheet = null;
        try {
            sheet = getSheet(new FileInputStream(file), file.getName());
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return sheet;
    }
    public static Sheet[] getAllSheets(File file) {
        Sheet[] sheets = null;
        try {
            sheets = getAllSheets(new FileInputStream(file), file.getName());
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return sheets;
    }
    private static Sheet getSheet(InputStream inputStream, String fileName) {
        Workbook hsswork = null;
        hsswork = getWorkbook(inputStream, fileName);
        Sheet sheet = hsswork.getSheetAt(0);
        return sheet;
    }
    private static Sheet[] getAllSheets(InputStream inputStream, String fileName) {
        Workbook hsswork = null;
        hsswork = getWorkbook(inputStream, fileName);
        int numberOfSheets = hsswork.getNumberOfSheets();
        Sheet[] sheets = new Sheet[numberOfSheets];
        for (int i = 0; i < numberOfSheets; i++) {
            sheets[i] = hsswork.getSheetAt(i);

        }
        return sheets;
    }



    /**
     * 根据文件名后缀获取不同的workbook
     *
     * @param input
     * @param fileName
     * @return
     */
    private static Workbook getWorkbook(InputStream input, String fileName) {
        Workbook wb = null;
        try {
            if (fileName == null) {

            } else if (fileName.toLowerCase().endsWith(EXCEL_2003L)) {
                wb = new HSSFWorkbook(input); // 2003-
            } else if (fileName.toLowerCase().endsWith(EXCEL_2007U)) {
                wb = new XSSFWorkbook(input); // 2007+
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;

    }
}
